import { connection } from "@/services/database";

export const generateUpdateFields = (params: { [key: string]: any }) => {
	return Object.entries(params)
		.filter(([_k, v]) => v !== void 0)
		.map(([k]) => k + " = :" + k)
		.join(", ");
};
export const generateInsertFields = (params: { [key: string]: any }) => {
	return (
		"(" +
		Object.keys(params)
			.filter(([_k, v]) => v !== void 0)
			.join(", ") +
		") VALUES (" +
		Object.keys(params)
			.filter(([_k, v]) => v !== void 0)
			.map((k) => ":" + k)
			.join(", ") +
		")"
	);
};
export const generateWhereFields = (params: { [key: string]: any }) => {
	const res = Object.entries(params)
		.filter(([_k, v]) => v !== void 0)
		.map(([k, v]) => k + " = " + v)
		.join(" AND ");
	return res ? " WHERE " + res : "";
};
export const generateLimitFields = (pageNumber: number, pageSize: number) => {
	return " LIMIT " + (pageNumber - 1) * pageSize + ", " + pageSize;
};
export const generateOrderByFields = (params: { [key: string]: any }) => {
	return Object.entries(params)
		.filter(([_k, v]) => v !== void 0)
		.map(([k]) => k + " " + params[k])
		.join(", ");
};
// 检查是否为用户
export const checkIsUser = async (userId: number) => {
	const [results] = (await connection.execute("SELECT COUNT(*) AS count FROM user WHERE id = :user_id", { user_id: userId })) as any;
	return results[0]?.count > 0;
};
// 检查用户是否是管理员
export const checkIsAdmin = async (callerId: number) => {
	const [results] = (await connection.execute("SELECT COUNT(*) AS count FROM admin WHERE user_id = :caller_id", { caller_id: callerId })) as any;
	return results[0]?.count > 0;
};
// 检查用户是否是老师
export const checkIsTeacher = async (callerId: number) => {
	const [results] = (await connection.execute("SELECT COUNT(*) AS count FROM teacher WHERE user_id = :caller_id", { caller_id: callerId })) as any;
	return results[0]?.count > 0;
};
// 检查用户是否是机构
export const checkIsAgency = async (callerId: number) => {
	const [results] = (await connection.execute("SELECT COUNT(*) AS count FROM agency WHERE user_id = :caller_id", { caller_id: callerId })) as any;
	return results[0]?.count > 0;
};
// 检查学生是否属于用户
export const checkIsStudentBelongUser = async (caller_id: number, student_id: number) => {
	const [results] = (await connection.execute(
		`SELECT COUNT(*) as count FROM user
			INNER JOIN student ON student.contact_phone_number = user.phone_number
			WHERE student.user_id = :student_id AND user.user_id = :caller_id`,
		{
			caller_id: caller_id,
			student_id: student_id,
		}
	)) as any;
	return results[0]?.count > 0;
};
// 检查报名信息是否属于用户
export const checkIsSignupBelongUser = async (caller_id: number, signup_id: number) => {
	const [results] = (await connection.execute(
		`SELECT COUNT(*) AS count
			FROM user
			INNER JOIN student ON student.contact_phone_number = user.phone_number
			INNER JOIN signup ON signup.user_id = student.user_id
			WHERE signup.signup_id = :signup_id AND user.user_id = :caller_id`,
		{
			caller_id: caller_id,
			signup_id: signup_id,
		}
	)) as any;
	return results[0]?.count > 0;
};
// 获取联系人手机号信息
export const getcontactPhoneNumberInfo = async (contactPhoneNumber: string): Promise<{ user_id: number; role: "teacher" | "agency" } | null> => {
	const [results] = (await connection.execute(
		`SELECT user.user_id,
			CASE 
				WHEN teacher.user_id IS NOT NULL THEN 'teacher'
				WHEN agency.user_id IS NOT NULL THEN 'agency'
				ELSE NULL
			END AS role
            FROM user
            LEFT JOIN student ON student.user_id = user.user_id
            LEFT JOIN teacher ON teacher.user_id = user.user_id
            LEFT JOIN agency ON agency.user_id = user.user_id
            WHERE student.contact_phone_number = :contact_phone_number`,
		{ contact_phone_number: contactPhoneNumber }
	)) as any;
	return results[0] ? { user_id: results[0].user_id, role: results[0].role } : null;
};
